const express = require('express');
const router = express.Router();
const fs = require('fs-extra')
const path = require('path')
// const mysql = require('mysql');
const xlsx = require('node-xlsx');
const db = require('../../sql/basicConnection')
const { responseSuccess, responseFailure, validListNull } = require('../../common/util')
const axios = require("axios");
const {websocketBroadcast} = require('../../common/websocketServer')
const {uuid} = require('../../common/util')
const dayjs = require('dayjs')


/**
 * 根据播种任务查询播种任务订单
 */

router.post('/sowManage/listOrdersBySowTaskNo', async (req, res) => {
    const params = req.body
    if(!params || !params.sowTaskNo){
        return responseFailure({
            res,
            message: 'sowTaskNo不能为空！'
        })
    }
    const sowListSql=`
    SELECT
        sow_task_item.order_item_id AS orderItemId,
        sku_list.id AS skuId,
        sku_list.name AS skuName,
        sku_list.pick_location_id AS pickLocationId,
        sku_list.pick_location_name As picLocationName,
        sow_task_item.pick_unit_total_count AS pickUnitTotalCount,
        sow_task_item.unit_total_count AS unitTotalCount,
        sow_task_item.sow_task_id AS sowTaskId,
        sow_task_item.id AS sowTaskItemId,
        sow_task_item.order_id AS orderId,
        sow_task_item.time_consuming AS timeConsuming,
        order_list.code AS orderNo,
        sow_task_item.destination AS destination
    FROM
        order_list
        INNER JOIN sow_task ON order_list.sow_task_id = sow_task.id
        INNER JOIN sow_task_item ON order_list.id = sow_task_item.order_id 
        INNER JOIN sku_list ON sow_task_item.sku_id = sku_list.id 
    WHERE
        sow_task.code = ?
    `
    try{
        const list = await db.query({
            sql:sowListSql,
            args: [params.sowTaskNo]
        })
        const dataObj = {},results = []
        list.forEach(row => {
            if(!dataObj[row.orderId]){
                dataObj[row.orderId] = []
            }
            dataObj[row.orderId].push(row)
        })
        Object.keys(dataObj).forEach(key=>{
            const currentData= dataObj[key]
            results.push({
                orderId:currentData[0].orderId,
                orderNo:currentData[0].orderNo,
                items:currentData.map(item=>{
                    delete item.orderId
                    delete item.orderNo
                    return {
                        ...item
                    }
                })
            })
        })
        responseSuccess({
            res,
            data:results
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }

})

// 播种任务项完成
router.post('/sowManage/complete', async (req, res) => {
    const params = req.body
    console.log(params)
    if(!params){
        return responseFailure({
            res,
            message: '参数缺失！'
        })
    }
    const {status,key} = validListNull(params,['orderItemId','sowTaskItemId','unitTotalCount'])
    if(!status){
        return responseFailure({
            res,
            message: key+'不能为空！'
        })
    }
    const sowUpdateSql=`
    UPDATE sow_task_item 
    SET unit_total_count = ?,
    time_consuming = ?
    WHERE
        id = ?
    `
    const currentSowTaskItemSql = `
    SELECT * FROM sow_task_item WHERE id = ?
    `
    try{
        await db.query({
            sql:sowUpdateSql,
            args: [params.unitTotalCount,params.time || 0,params.sowTaskItemId,]
        })
        const currentSowTaskItem = await db.query({
            sql:currentSowTaskItemSql,
            args: [params.sowTaskItemId]
        })
        const sowTaskItemListSql = `
        SELECT * FROM sow_task_item WHERE sow_task_id = ?
        `
        const sowTaskItemList = await db.query({
            sql:sowTaskItemListSql,
            args: [currentSowTaskItem[0].sow_task_id]
        })
        // 判断当前任务的所有子项是否完成
        if(sowTaskItemList.every(item=>item.pick_unit_total_count === item.unit_total_count)){
            const updateSowTaskStatusSql = `
            UPDATE sow_task
            SET status=2
            WHERE
                id = ?
            `
            await db.query({
                sql: updateSowTaskStatusSql,
                args: [currentSowTaskItem[0].sow_task_id]
            })
            console.log('当前播种任务完成')
        }
        responseSuccess({
            res
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }

})

// 通知websocket进行订单推送
router.get('/websocketServer/taskPush', (req, res) => {
    const params = req.query
    if(!params || !params.sowTaskNo){
        return responseFailure({
            res,
            message: 'sowTaskNo不能为空'
        })
    }
    axios.post('http://localhost:8095/xunfei/sowManage/listOrdersBySowTaskNo',{
        "sowTaskNo": params.sowTaskNo
    }).then(cuRes=>{
        console.log(cuRes.data)
        try {
            // 简化数据
            const list = []
            cuRes.data.data.forEach(data => {
                data.items.forEach(item => {
                    list.push({
                        sowTaskId: params.sowTaskNo,
                        skuId: item.skuId,
                        skuName: item.skuName,
                        pickUnitTotalCount: item.pickUnitTotalCount,
                        sowTaskItemId: item.sowTaskItemId,
                        destination: item.destination
                    })
                })
            })
            websocketBroadcast(JSON.stringify({
                action: "ITEMPUSH_FROM_SERVER",
                data: list
            }))
            responseSuccess({
                res
            })
        }catch (e){
            responseFailure({
                res,
                message:e
            })
        }
    }).catch(err=>{
        responseFailure({
            res,
            message:err
        })
    })
})

// 创建播种订单
router.post('/sowManage/addSowTask', async (req, res) => {
    const list = req.body.skuList
    if(!list || !list.length){
        return responseFailure({
            res,
            message:'skuList不能为空'
        })
    }

    const orderId = uuid(10,9)
    const orderItemId = uuid(10,9)
    const sowTaskId = uuid(10,9)
    try{
        // 创建播种项数据
        for(let i=0;i<list.length;i++){
            const insertSql = `
        INSERT INTO sow_task_item (id,order_item_id,sow_task_id,order_id,sku_id,pick_unit_total_count,unit_total_count,destination)
        VALUES (?,?,?,?,?,?,0,?)
        `
            await db.query({
                sql:insertSql,
                args: [`${uuid(10,9)}`,`${orderItemId}`,`${sowTaskId}`,`${orderId}`,`${list[i].skuId}`,`${list[i].pickUnitTotalCount}`,`${list[i].destination}`]
            })
        }

        // 创建订单数据
        const insertOrderSql = `
        INSERT INTO order_list (id,code,sow_task_id)
        VALUES (?,?,?)
        `
        await db.query({
            sql:insertOrderSql,
            args: [`${orderId}`,`${orderId}-2`,`${sowTaskId}`]
        })

        // 创建播种任务
        const insertSowSql = `
        INSERT INTO sow_task (id,code,status)
        VALUES (?,?,0)
        `
        await db.query({
            sql:insertSowSql,
            args: [`${sowTaskId}`,`${sowTaskId}`]
        })

        // 推送播种任务
        await axios.get('http://localhost:8095/xunfei/websocketServer/taskPush',{
            params:{
                sowTaskNo: sowTaskId
            }
        })
    }catch (e){
        return responseFailure({
            res,
            message:e
        })
    }

    responseSuccess({
        res,
        data:{
            sowTaskId
        }
    })
})

// 随机创建播种订单
router.post('/sowManage/addRandomSowTask', async (req, res) => {
    const {skuNum,skuMaxCount,destination} = req.body
    if(!skuNum){
        return responseFailure({
            res,
            message:'skuNum不能为空'
        })
    }
    if(!skuMaxCount){
        return responseFailure({
            res,
            message:'skuMaxCount不能为空'
        })
    }
    let skuList = []
    const skuListSql=`
    SELECT * FROM sku_list
    `
    try{
        skuList = await db.query({
            sql:skuListSql,
            args: null
        })
        // 品类不足时，按照所有品类
        if(skuList.length<=skuNum){
            skuList = skuList.map(item=>{
                return {
                    skuId: item.id,
                    pickUnitTotalCount: Math.ceil(Math.random()*skuMaxCount),
                    destination
                }
            })
        }else{
            // 品类足够时，随机抽取，不取重复
            const randomSkuList = []
            for(let i=0;i<skuNum;i++){
                const index = Math.floor(Math.random()*skuList.length)
                randomSkuList.push(skuList[index])
                skuList.splice(index,1)
            }
            skuList = randomSkuList.map(item=>{
                return {
                    skuId: item.id,
                    pickUnitTotalCount: Math.ceil(Math.random()*skuMaxCount),
                    destination
                }
            })
        }
        console.log(skuList)
        const response = await axios.post('http://localhost:8095/xunfei/sowManage/addSowTask',{
            "skuList": skuList
        })

        if(response.data.success){
            responseSuccess({
                res,
                data:response.data.data
            })
        }else{
            responseFailure({
                res,
                message:response.data.message
            })
        }
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 汇总今日已拣订单、今日已拣商品、等待分拣订单、等待分拣商品信息
router.get('/sowManage/todaySowMsg', async (req, res) => {
    try{
        // 今日已拣订单
        const todaySowListSql = `
           SELECT * FROM sow_task WHERE TO_DAYS(create_time) = TO_DAYS(NOW()) AND status = 2
        `
        const todaySowList = await db.query({
            sql:todaySowListSql,
            args: null
        })

        // 当天任务总耗时（分钟）
        const todayTimeConsumingSql = `
            SELECT SUM(time_consuming) as time_consuming FROM sow_task_item WHERE TO_DAYS(create_time) = TO_DAYS(NOW())
        `
        const todayTimeConsuming = await db.query({
            sql:todayTimeConsumingSql,
            args: null
        })


        // 今日已拣商品
        const todayGoodsListSql = `
            SELECT * FROM sow_task_item WHERE TO_DAYS(create_time) = TO_DAYS(NOW()) AND pick_unit_total_count = unit_total_count
        `
        const todayGoodsList = await db.query({
            sql:todayGoodsListSql,
            args: null
        })

        // 等待分拣商品
        const waitGoodsListSql = `
            SELECT * FROM sow_task_item WHERE TO_DAYS(create_time) = TO_DAYS(NOW()) AND pick_unit_total_count > unit_total_count
        `
        const waitGoodsList = await db.query({
            sql:waitGoodsListSql,
            args: null
        })

        responseSuccess({
            res,
            data:{
                todaySowNum: todaySowList.length,
                todayGoodsNum: todayGoodsList.length,
                todaySowTimeConsuming: Number(todayTimeConsuming[0].time_consuming || 0),
                waitGoodsNum: waitGoodsList.length
            }
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 今日分拣商品时段分布
router.get('/sowManage/todayGoodsByTimes', async (req, res) => {
    const times = ['4:00','8:00','12:00','16:00','20:00','23:59']
    let result = []
    try{
        for(let i = 0; i < times.length; i++){
            const currentTimeCountSql = `
            SELECT
              *
            FROM
               sow_task_item 
            WHERE
                pick_unit_total_count = unit_total_count 
                AND create_time BETWEEN '${dayjs().format('YYYY-MM-DD ' + (i==0?'00:00':times[i-1]))}' 
                AND '${dayjs().format('YYYY-MM-DD '+times[i])}'
            `
            const currentTimeCountList = await db.query({
                sql:currentTimeCountSql,
                args: null
            })
            result.push({
                time: i==(times.length-1)?'24:00':times[i],
                num: currentTimeCountList.length
            })
        }
        responseSuccess({
            res,
            data:{
                result
            }
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 今日实时分拣商品信息
router.get('/sowManage/todayWorkingGoodsRealTime', async (req, res) => {
    const workingGoodsSql = `
    SELECT
        sow_task_item.id AS sowTaskItemId,
        sow_task_item.sow_task_id AS sowTaskId,
        sow_task_item.order_id AS orderId,
        sow_task_item.sku_id AS skuId,
        sku_list.name AS skuName,
        sow_task_item.pick_unit_total_count AS pickUnitTotalCount,
        sow_task_item.unit_total_count AS unitTotalCount, 
        sow_task_item.time_consuming AS timeConsuming 
    FROM
        sow_task
        INNER JOIN order_list ON sow_task.id = order_list.sow_task_id
        INNER JOIN sow_task_item ON order_list.id = sow_task_item.order_id
        INNER JOIN sku_list ON sow_task_item.sku_id = sku_list.id 
    WHERE
        sow_task.STATUS = 1
    `
    try{
        const workingGoodsList = await db.query({
            sql:workingGoodsSql,
            args: null
        })
        responseSuccess({
            res,
            data:{
                workingGoodsList
            }
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 删除等待分拣的所有任务，演示用
router.delete('/sowManage/removeWaitingSowTask', async (req, res) => {
    try{
        const waitingSowTaskSql = `SELECT * FROM sow_task WHERE status=0`
        const waitingTaskList = await db.query({
            sql: waitingSowTaskSql,
            args: null
        })
        for(let i = 0; i < waitingTaskList.length; i++){
            // 删除任务项
            const deleteSowTaskItemSql = `DELETE FROM sow_task_item WHERE sow_task_id=?`
            await db.query({
                sql: deleteSowTaskItemSql,
                args: [`${waitingTaskList[i].id}`]
            })
            // 删除订单
            const deleteOrderListSql = `DELETE FROM order_list WHERE sow_task_id=?`
            await db.query({
                sql: deleteOrderListSql,
                args: [`${waitingTaskList[i].id}`]
            })
            // 删除任务
            const deleteSowTaskSql = `DELETE FROM sow_task WHERE id=?`
            await db.query({
                sql: deleteSowTaskSql,
                args: [`${waitingTaskList[i].id}`]
            })
        }
        responseSuccess({
            res
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }

})

// 查询所有sku信息
router.get('/skuManage/getAllSkus', async (req, res) => {
    const getAllSkusSql = `
    SELECT
        sku_list.id AS id,
        sku_list.\`name\` AS \`name\`,
        sku_list.pick_location_id AS pickLocationId,
        sku_list.pick_location_name AS pickLocationName,
        sku_list.price AS price,
        sku_list.picture_url AS pictureUrl
    FROM
        sku_list
    `
    try{
        const allSkusList = await db.query({
            sql: getAllSkusSql,
            args: null
        })
        responseSuccess({
            res,
            data:{
                skuList: allSkusList
            }
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 根据sowTaskItemId更新正在播种的任务
router.post('/sowManage/updateWorkingSowTask', async (req, res) => {
    const {sowTaskItemId} = req.body
    if(!sowTaskItemId){
        return responseFailure({
            res,
            message:'sowTaskItemId不能为空'
        })
    }
    const currentWorkingSowTaskSql = `SELECT * FROM sow_task WHERE status=1`
    const targetSowTaskSql = `SELECT * FROM sow_task_item WHERE id=?`
    try{
        const targetSowTask = await db.query({
            sql: targetSowTaskSql,
            args: [`${sowTaskItemId}`]
        })
        if(!targetSowTask || targetSowTask.length==0){
            return responseFailure({
                res,
                message:'待播种任务项不存在'
            })
        }
        const currentWorkingSowTaskList = await db.query({
            sql: currentWorkingSowTaskSql,
            args: null
        })
        let hasSameSowTask = false
        // 已经存在正在执行的任务，则判断状态改为未进行
        if(currentWorkingSowTaskList.length>0){
            for(let i= 0;i<currentWorkingSowTaskList.length;i++){
                if(currentWorkingSowTaskList[i].id === targetSowTask[0].sow_task_id){
                    hasSameSowTask = true
                    console.log('相同的播种任务')
                    continue
                }
                const updateSql = `UPDATE sow_task SET status=0 WHERE id=?`
                await db.query({
                    sql: updateSql,
                    args: [`${currentWorkingSowTaskList[i].id}`]
                })
            }
        }
        // 未更新当前目标任务，则更新
        if(!hasSameSowTask){
            const updateSql = `UPDATE sow_task SET status=1 WHERE id=?`
            await db.query({
                sql: updateSql,
                args: [`${targetSowTask[0].sow_task_id}`]
            })
        }
        responseSuccess({
            res
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 获取未进行的所有任务项的id
router.get('/sowManage/getAllUnStartSowTaskItems', async (req, res) => {
    const getAllUnStartSowTaskItemsSql = `
    SELECT
        sow_task_item.id 
    FROM
        sow_task_item
        INNER JOIN sow_task ON sow_task_item.sow_task_id = sow_task.id 
    WHERE
        sow_task.STATUS =0
    `
    try{
        const unfinishedSowTaskItemsList = await db.query({
            sql: getAllUnStartSowTaskItemsSql,
            args: null
        })
        responseSuccess({
            res,
            data:unfinishedSowTaskItemsList.map(item=>item.id)
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }
})

// 通知websocket发送所有未进行的所有任务项的id
router.get('/websocketServer/callRemoveTasks', async (req, res) => {
    try{
        const result = await axios.get('http://localhost:8095/xunfei/sowManage/getAllUnStartSowTaskItems')
        websocketBroadcast(JSON.stringify({
            action: "ITEMCANCEL_FROM_SERVER",
            data: result.data.data
        }))
        responseSuccess({
            res
        })
    }catch (e){
        responseFailure({
            res,
            message:e
        })
    }

})

module.exports = router;
